package youleche.com.sql.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import youleche.com.connection.JdbcHelper;
import youleche.com.sql.idao.ITableField;
import youleche.com.sql.model.MTableField;

/**
 * 表字段数据访问层
 * @author Administrator
 *
 */
public class DTableField implements ITableField {
	/**
	 * 根据tableId获取字段
	 * @param con 数据库连接
	 * @param tableId 表ID
	 * @return 返回所有字段
	 * @throws Exception 异常
	 */
	@Override
	public List<MTableField> getTableFieldsByTableId(Connection con,
			String tableId) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql ="select " +
					"fieldChineseName,fieldEnglishName,fieldType,fieldId,fieldLength," +
					"isNull,isPrimaryKey,fieldRelation,isUniqueIndex,isIndex," +
					"isAutoAdd,fieldInitValue,fieldStyle,fieldValueYueShu,fieldDesc," +
					"fieldAddPerson,fieldAddTime,fieldModifyTime,tableId " +
					"from tableField" +
					" where isDelete=0 and tableId='" + tableId + "' order by fieldId asc;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MTableField field = null;
			List<MTableField> list = null;
			if(rs != null){
				list = new LinkedList<MTableField>();
				while(rs.next()){
					field = new MTableField();
					field.setFieldChineseName(rs.getString(1));
					field.setFieldEnglishName(rs.getString(2));
					field.setFieldType(rs.getString(3));
					field.setFieldId(rs.getInt(4));
					field.setFieldLength(rs.getString(5));
					field.setIsNull(rs.getInt(6));
					field.setIsPrimaryKey(rs.getInt(7));
					field.setFieldRelation(rs.getString(8));
					field.setIsUniqueIndex(rs.getInt(9));
					field.setIsIndex(rs.getInt(10));
					field.setIsAutoAdd(rs.getInt(11));
					field.setFieldInitValue(rs.getString(11));
					field.setFieldStyle(rs.getString(12));
					field.setFieldValueYueShu(rs.getString(14));
					field.setFieldDesc(rs.getString(15));
					field.setTableId(rs.getString(19));
					list.add(field);
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 添加表字段
	 * @param con 数据库连接
	 * @param tablefield 表字段对象
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int addTableFields(Connection con, MTableField tablefield)
			throws Exception {
		PreparedStatement pre = null;
		try{
			String sql = "insert tablefield(" +
					"fieldId,fieldChineseName,fieldEnglishName,fieldType,fieldLength," +
					"tableId,isNull,fieldValueYueShu,fieldDesc,isPrimaryKey," +
					"isUniqueIndex,isIndex,fieldInitValue) " +
					"value(?,?,?,?,?,?,?,?,?,?,?,?,?);";
			pre = con.prepareStatement(sql);
			pre.setObject(1, tablefield.getFieldId());
			pre.setObject(2, tablefield.getFieldChineseName());
			pre.setObject(3, tablefield.getFieldEnglishName());
			pre.setObject(4, tablefield.getFieldType());
			pre.setObject(5, tablefield.getFieldLength());
			pre.setObject(6, tablefield.getTableId());
			pre.setObject(7, tablefield.getIsNull());
			pre.setObject(8, tablefield.getFieldValueYueShu());
			pre.setObject(9, tablefield.getFieldDesc());
			pre.setObject(10, tablefield.getIsPrimaryKey());
			pre.setObject(11, tablefield.getIsUniqueIndex());
			pre.setObject(12, tablefield.getIsIndex());
			pre.setObject(13, tablefield.getFieldInitValue());
			
			return pre.executeUpdate();
		}finally{
			JdbcHelper.free(null, pre);
		}
	}

	/**
	 * 获取最大的字段ID
	 * @param con 数据库连接
	 * @param tableId 表ID
	 * @return 返回最大的fieldId
	 * @throws Exception 异常
	 */
	@Override
	public int getMaxFieldIdByTableId(Connection con, String tableId)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select max(fieldId) from tablefield where tableid='" + tableId + "' limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					return rs.getInt(1);
				}
			}
			
			return 0;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 得到备用字段的数量
	 * @param con 数据库连接
	 * @return 返回备用字段的数量
	 * @throws Exception 异常
	 */
	@Override
	public int getReadyFieldNum(Connection con, String tableId) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(fieldId) from tablefield where fieldChineseName like '备用字段%' and fieldEnglishName like 'A%' and isnull=0 and tableId='"+tableId+"' and isDelete=0" ;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					return rs.getInt(1);
				}
			}
			
			return 0;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 判断是否存在该字段
	 * @param con 数据库连接
	 * @param englishName 字段英文名称
	 * @param tableId 表ID
	 * @return 返回是否存在该字段 true-存在
	 * @throws Exception 异常
	 */
	@Override
	public boolean isExistFieldByFieldEnglishNameAndTableId(Connection con,
			String englishName, String tableId) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select  fieldEnglishName from tablefield " +
					"where tableId='"+tableId+"' and fieldEnglishName='"+englishName+"';";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					return !"".equals(rs.getString(1));
				}
			}
			
			return false;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 通过表ID和英文名称更新字段
	 * @param con 数据库连接
	 * @param tablefield 字段信息
	 * @return 返回更新影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int updateFieldByIdAndEnglishName(Connection con,
			MTableField tablefield) throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update tablefield set" +
					" fieldDesc='"+tablefield.getFieldDesc()+"', fieldLength='"+ tablefield.getFieldLength()+"'" +
					" where " +
					"tableId='"+tablefield.getTableId()+"' and fieldEnglishName='"+tablefield.getFieldEnglishName()+"'";
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}
	
}
